import os
from time import sleep
import pymysql
import pandas as pd
from datetime import datetime 
from pymysql.converters import escape_string
from pymysql.converters import escape_date
import re

class toMysql:
    def __init__(self):
        self.host='192.168.0.113'
        self.port=3306
        self.user='coname'
        self.passwd='xZS6p4LDcKCwn4Yb'
        self.charset='utf8'
        self.db='co'

    def open_mysql(self):#打开数据库
        try:
            self.conn = pymysql.connect(host=self.host,port=self.port,user=self.user,passwd=self.passwd,db=self.db,charset=self.charset)
            self.cursor=self.conn.cursor()
            print("连接数据库成功!")
            return self.conn,self.cursor
        except Exception as e:
            print("连接数据库失败>>>",e)

    def close_mysql(self,conn,cursor):#关闭数据库
        try:
            cursor.close()
            sleep(1)
            conn.close()
            print("关闭数据库成功!")
        except Exception as e:
            print("关闭数据失败！>>>",e)

    def input_mysql(self,datas):#存入数据库 会员卡
        conn,cursor = self.open_mysql()
        try:
            if not cursor.execute('select phone from co_meiguanjia_vip where phone ="{}"'.format(datas[2])):
                cursor.execute('insert into co_meiguanjia_vip (keywordid,srname,phone,birthday,card,cardtype,zhekou,yue,send,enddate,startdate) values ("{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}")'.format(datas[0],datas[1],datas[2],datas[3],datas[4],datas[5],datas[6],datas[7],datas[8],datas[9],datas[10]))
                conn.commit()
                print('插入会员"{}"'.format(datas[1]))
        except Exception as e:
            print('插入会员卡数据上失败>>>',e)
        self.close_mysql(conn,cursor)

    def input_mysql_taocan(self,datas):#插入数据库  套餐
        conn,cursor = self.open_mysql()
        try:
            if not cursor.execute('select phone from co_meiguanjia_taocan where phone ="{}" and taocan="{}"'.format(datas[2],datas[3])):
                cursor.execute('insert into co_meiguanjia_taocan (keywordid,srname,phone,taocan) values ("{}","{}","{}","{}")'.format(datas[0],datas[1],datas[2],datas[3]))
                conn.commit()
                print('插入会员"{}"'.format(datas[1]))
        except Exception as e:
            print('插入会员卡数据上失败>>>',e)
        self.close_mysql(conn,cursor)

    def input_mysql_sole(self,datas):#插入数据库  消费记录
        conn,cursor = self.open_mysql()
        try:
            if not cursor.execute('select phone from co_meiguanjia_sole where phone ="{}" and sole="{}" and solet="{}"'.format(datas[2],datas[4],escape_string(datas[6]))):
                cursor.execute('insert into co_meiguanjia_sole (keywordid,srname,phone,ctype,sole,mendian,solet,cstatus) values ("{}","{}","{}","{}","{}","{}","{}","{}")'.format(datas[0],datas[1],datas[2],datas[3],datas[4],datas[5],escape_string(datas[6]),datas[7]))
                conn.commit()
                print('插入会员"{}"'.format(datas[1]))
        except Exception as e:
            print('插入会员卡数据上失败>>>',e)
        self.close_mysql(conn,cursor)

    def oupput_mysql(self):
        conn,cursor = self.open_mysql()
        try:
            cursor.execute('select srname,keywordid from co_meiguanjia')
            datas = cursor.fetchall()
            return datas
        except Exception as e:
            print('取出数据失败>>>',e)
        self.close_mysql(conn,cursor)

    def update(self,data):
        conn,cursor = self.open_mysql()
        try:
            cursor.execute('update co_meiguanjia_taocan set srname ="{}" where pid = "{}"'.format(data[0],data[1]))
            conn.commit()
            print('更新数据成功!') 
        except Exception as e:
            print('更改失败>>>',e)
        self.close_mysql(conn,cursor)

    def read_exce(self):#读取excel                                         '\sum1.xlsx' '\sum2.xlsx
        datas = pd.read_excel(os.path.dirname(os.path.realpath(__file__))+'\sum3.xlsx',sheet_name="Sheet1")
        num = datas.shape[0]
        tamp = '{0:%y%m%d%H%M%S}'.format(datetime.now())
        tamp = int(tamp) 
        for i in range(0,num,1):
            tamp = tamp+1
            data = []
            data.append(str(tamp))
            data.append(datas.loc[i,'srname']) 
            data.append(datas.loc[i,'phone'])
            data.append(datas.loc[i,'ctype'])
            data.append(datas.loc[i,'sole'])
            data.append(datas.loc[i,'mendian'])
            data.append(datas.loc[i,'soletime'])
            data.append(datas.loc[i,'cstatus'])
            self.input_mysql_sole(data)

    def run(self):
        self.read_exce()  
            
    def run2(self):
        datas = self.oupput_mysql()
        for data in datas:
            dataes = []
            dat = data[0].replace('\n','').replace('\r','').replace('\t','').replace(' ','')
            print(dat)
            dataes.append(dat)
            dataes.append(data[1])
            self.update(dataes)

if __name__ == "__main__":
    
    toMysql().run2()